<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Sis_ajax extends MY_Controller {
	
	function __construct()
	{
		parent::__construct(); 
		
		// Uses Model
		$this->load->model('sis_store_procedure_model', 'SP');
		$this->load->model('sis_anggota_model', 'Anggota');
		$this->load->model('combo_model', 'Combo');
		
		// Secure Page With Session
		$this->secure_session();
	}
	
	function autocomplete_perusahaan_by_kode(){
		$ls_keyword	= get('term');
		
		$ls_sql	= "	SELECT	*
					FROM	(
								SELECT	id_perusahaan, kode_perusahaan, nama_perusahaan, 
										kode_perusahaan||' - '||nama_perusahaan display,
										ROW_NUMBER () OVER (ORDER BY kode_perusahaan) AS row_number
								FROM	sw_perusahaan
								WHERE	UPPER(NVL(kode_perusahaan,'')) LIKE '%".strtoupper($ls_keyword)."%'
							) TMP
					WHERE	row_number <=30
					ORDER BY row_number";
		
		$result	= $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			echo json_encode($result->result_array());
		}else{
			echo '[]';
		}
	}
	
	function autocomplete_perusahaan_by_nama(){
		$ls_keyword	= get('term');
		
		$ls_sql	= "	SELECT	*
					FROM	(
								SELECT	id_perusahaan, kode_perusahaan, nama_perusahaan, 
										kode_perusahaan||' - '||nama_perusahaan display,
										ROW_NUMBER () OVER (ORDER BY nama_perusahaan) AS row_number
								FROM	sw_perusahaan
								WHERE	UPPER(NVL(nama_perusahaan,'')) LIKE '%".strtoupper($ls_keyword)."%'
							) TMP
					WHERE	row_number <=30
					ORDER BY row_number";
		
		$result	= $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			echo json_encode($result->result_array());
		}else{
			echo '[]';
		}
	}
	
	function autocomplete_anggota_by_kode(){
		$st_aktif	= get('st_aktif');
		$ls_keyword	= get('term');
		
		$ls_sql	= "	SELECT	*
					FROM	(
								SELECT	a.id_anggota, a.kd_bank, d.nama_bank, a.nama_cabang, a.no_rekening, a.pemilik_rekening, b.full_name,c.kd_jenis_anggota,c.nama_jenis_anggota,
                                        a.id_anggota||' - '||b.full_name display,
										F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'POKOK', SYSDATE) SIMPANAN_POKOK,
										F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'WAJIB', SYSDATE) SIMPANAN_WAJIB,
										F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'SUKARELA', SYSDATE) SIMPANAN_SUKARELA,
                                        ROW_NUMBER () OVER (ORDER BY a.id_anggota) AS row_number
                                FROM    sis_anggota a
                                        join sw_person b ON a.person_id = b.person_id
                                        left join sis_setup_jenis_anggota c on a.kd_jenis_anggota = c.kd_jenis_anggota 
										left join keu_bank d on d.kd_bank = a.kd_bank 
								WHERE	UPPER(NVL(a.id_anggota,'')) LIKE '%".strtoupper($ls_keyword)."%'
										".($st_aktif == '' ? '' : " AND NVL(a.st_aktif,'0') = '".$st_aktif."' ")."
							) TMP
					WHERE	row_number <=30
					ORDER BY row_number";
		
		$result	= $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			echo json_encode($result->result_array());
		}else{
			echo '[]';
		}
	}
	
	function autocomplete_anggota_by_nama(){
		$st_aktif	= get('st_aktif');
		$ls_keyword	= get('term');
		
		$ls_sql	= "	SELECT	*
					FROM	(
								SELECT	a.id_anggota, a.kd_bank, d.nama_bank, a.nama_cabang, a.no_rekening, a.pemilik_rekening, b.full_name,c.kd_jenis_anggota,c.nama_jenis_anggota,
                                        a.id_anggota||' - '||b.full_name display,
										F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'POKOK', SYSDATE) SIMPANAN_POKOK,
										F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'WAJIB', SYSDATE) SIMPANAN_WAJIB,
										F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'SUKARELA', SYSDATE) SIMPANAN_SUKARELA,
                                        ROW_NUMBER () OVER (ORDER BY a.id_anggota) AS row_number
                                FROM    sis_anggota a
                                        join sw_person b ON a.person_id = b.person_id
                                        left join sis_setup_jenis_anggota c on a.kd_jenis_anggota = c.kd_jenis_anggota 
										left join keu_bank d on d.kd_bank = a.kd_bank 
								WHERE	UPPER(NVL(b.full_name,'')) LIKE '%".strtoupper($ls_keyword)."%'
										".($st_aktif == '' ? '' : " AND NVL(a.st_aktif,'0') = '".$st_aktif."' ")."
							) TMP
					WHERE	row_number <=30
					ORDER BY row_number";
		
		$result	= $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			echo json_encode($result->result_array());
		}else{
			echo '[]';
		}
	}
	
	function get_dokumen_daftar(){
		$ls_sql	= "	SELECT DOK.ID_DOKUMEN,
						   DOK.NAMA_DOKUMEN,
						   TO_CHAR (DFTDOK.TGL_TERIMA, 'DD/MM/YYYY') TGL_TERIMA,
						   NVL(ST_MANDATORY,'N') ST_MANDATORY
					  FROM SIS_SETUP_GRUP_DOKUMEN GRPDOK
						   JOIN SIS_SETUP_GRUP_DOKUMEN_DETAIL GRPDOKDTL
							  ON GRPDOK.KD_GRUP_DOKUMEN = GRPDOKDTL.KD_GRUP_DOKUMEN
						   JOIN SIS_SETUP_DOKUMEN DOK
							  ON DOK.ID_DOKUMEN = GRPDOKDTL.ID_DOKUMEN
						   LEFT JOIN SIS_ANGGOTA_DOKUMEN_DAFTAR DFTDOK
							  ON DFTDOK.ID_DOKUMEN = DOK.ID_DOKUMEN AND DFTDOK.ID_ANGGOTA = '".get('id_anggota')."'
					 WHERE GRPDOK.KD_GRUP_DOKUMEN = 'DOKGRP0001'
						   AND GRPDOKDTL.KD_JENIS_ANGGOTA = '".get('kd_jenis_anggota')."'
					ORDER BY NVL(ST_MANDATORY,'N') DESC, DOK.NAMA_DOKUMEN";
		$result	= $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			echo json_encode($result->result_array());
		}else{
			echo '[]';
		}
	}
	
	function get_dokumen_pinjaman(){
		$ls_sql	= "	SELECT DOK.ID_DOKUMEN, DOK.NAMA_DOKUMEN, TO_CHAR(PJMDOK.TGL_TERIMA,'DD/MM/YYYY') TGL_TERIMA
					  FROM SIS_SETUP_GRUP_DOKUMEN GRPDOK
						   JOIN SIS_SETUP_GRUP_DOKUMEN_DETAIL GRPDOKDTL
							  ON GRPDOK.KD_GRUP_DOKUMEN = GRPDOKDTL.KD_GRUP_DOKUMEN
						   JOIN SIS_SETUP_DOKUMEN DOK
							  ON DOK.ID_DOKUMEN = GRPDOKDTL.ID_DOKUMEN
						   LEFT JOIN SIS_PINJAMAN_DOKUMEN PJMDOK
							  ON PJMDOK.ID_DOKUMEN = DOK.ID_DOKUMEN AND PJMDOK.ID_PINJAMAN = '".get('id_pinjaman')."'
					 WHERE GRPDOK.KD_GRUP_DOKUMEN = 'DOKGR".get('kode_jenis_pinjaman')."'
					 		AND GRPDOKDTL.KD_JENIS_ANGGOTA = '".get('kd_jenis_anggota')."'";
		$result	= $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			echo json_encode($result->result_array());
		}else{
			echo '[]';
		}
	}
	
	function get_dokumen_keluar(){
		$ls_sql	= "	SELECT DOK.ID_DOKUMEN,
						   DOK.NAMA_DOKUMEN,
						   TO_CHAR (DFTDOK.TGL_TERIMA, 'DD/MM/YYYY') TGL_TERIMA,
						   NVL(ST_MANDATORY,'N') ST_MANDATORY
					  FROM SIS_SETUP_GRUP_DOKUMEN GRPDOK
						   JOIN SIS_SETUP_GRUP_DOKUMEN_DETAIL GRPDOKDTL
							  ON GRPDOK.KD_GRUP_DOKUMEN = GRPDOKDTL.KD_GRUP_DOKUMEN
						   JOIN SIS_SETUP_DOKUMEN DOK
							  ON DOK.ID_DOKUMEN = GRPDOKDTL.ID_DOKUMEN
						   LEFT JOIN SIS_ANGGOTA_DOKUMEN_KELUAR DFTDOK
							  ON DFTDOK.ID_DOKUMEN = DOK.ID_DOKUMEN AND DFTDOK.ID_ANGGOTA_KELUAR = '".get('id_anggota_keluar')."'
					 WHERE GRPDOK.KD_GRUP_DOKUMEN = 'DOKGRP0003'
						   AND GRPDOKDTL.KD_JENIS_ANGGOTA = '".get('kd_jenis_anggota')."'
					ORDER BY NVL(ST_MANDATORY,'N') DESC, DOK.NAMA_DOKUMEN";
		$result	= $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			echo json_encode($result->result_array());
		}else{
			echo '[]';
		}
	}
	
	function get_limit_kredit(){
		$ls_eff_date	= get('tgl_efektif');
		if($ls_eff_date==''){ $ls_eff_date = date_format3(NOW,'DD/MM/YYYY'); }
		$arr_data	= $this->Anggota->GetDetailLimitKredit(get('id_anggota'), get('kd_jenis_anggota'), $ls_eff_date);
		if($arr_data != NULL && count($arr_data) > 0){
			echo json_encode($arr_data);
		}else{
			echo '[]';
		}
	}
	
	function get_parameter_simpanan(){
		$simpanan_pokok		= $this->get_data("SELECT F_SIS_GET_VARIABEL_PARAMETER('POKOK', '".get('kd_jenis_anggota')."', TO_DATE ('".date_format3(NOW,'DD/MM/YYYY')."', 'DD/MM/YYYY')) FROM DUAL");
		$simpanan_wajib		= $this->get_data("SELECT F_SIS_GET_VARIABEL_PARAMETER('WAJIB', '".get('kd_jenis_anggota')."', TO_DATE ('".date_format3(NOW,'DD/MM/YYYY')."', 'DD/MM/YYYY')) FROM DUAL");
		$biaya_kartu_utama		= $this->get_data("SELECT F_SIS_GET_VARIABEL_PARAMETER('BIAYA_KARTU_UTAMA', '".get('kd_jenis_anggota')."', TO_DATE ('".date_format3(NOW,'DD/MM/YYYY')."', 'DD/MM/YYYY')) FROM DUAL");
		$biaya_kartu_tambahan	= $this->get_data("SELECT F_SIS_GET_VARIABEL_PARAMETER('BIAYA_KARTU_TAMBAHAN', '".get('kd_jenis_anggota')."', TO_DATE ('".date_format3(NOW,'DD/MM/YYYY')."', 'DD/MM/YYYY')) FROM DUAL");
		
		echo json_encode(array(	'simpanan_pokok' 	=> $simpanan_pokok,
								'simpanan_wajib' 	=> $simpanan_wajib,
								'biaya_kartu_utama'		=> $biaya_kartu_utama,
								'biaya_kartu_tambahan'	=> $biaya_kartu_tambahan));
	}
	
	function get_info_anggota(){
		$data	= $this->Anggota->GetSingleView(get('id_anggota'));
		
		
		$tgl_effektif	= (trim(get('tgl_effektif'))=='' ? NOW : get('tgl_effektif'));
		$data['saldo_simpanan_pokok']	= $this->get_data("SELECT F_SIS_GET_SALDO_ANGGOTA('".get('id_anggota')."', 'POKOK', TO_DATE ('".date_format3($tgl_effektif,'DD/MM/YYYY')."', 'DD/MM/YYYY')) FROM DUAL");
		$data['saldo_simpanan_wajib']	= $this->get_data("SELECT F_SIS_GET_SALDO_ANGGOTA('".get('id_anggota')."', 'WAJIB', TO_DATE ('".date_format3($tgl_effektif,'DD/MM/YYYY')."', 'DD/MM/YYYY')) FROM DUAL");
		$data['saldo_simpanan_sukarela']= $this->get_data("SELECT F_SIS_GET_SALDO_ANGGOTA('".get('id_anggota')."', 'SUKARELA', TO_DATE ('".date_format3($tgl_effektif,'DD/MM/YYYY')."', 'DD/MM/YYYY')) FROM DUAL");
		$data['saldo_simpanan_sijaka']	= $this->get_data("SELECT F_SIS_GET_SALDO_ANGGOTA('".get('id_anggota')."', 'SIJAKA', TO_DATE ('".date_format3($tgl_effektif,'DD/MM/YYYY')."', 'DD/MM/YYYY')) FROM DUAL");
		
		echo json_encode($data);
	}
	
	function check_duplikat_no_kartu(){
		if(intval($this->get_data("	SELECT	COUNT(1) JML 
									FROM	SIS_ANGGOTA_KARTU 
									WHERE	ID_KARTU <> '".(post('id_kartu')==''?'X':post('id_kartu'))."' AND NO_KARTU = '".post('no_kartu')."'")) > 0)
		{
			echo 'DUPLICATE';
		}else{
			echo 'EMPTY';
		}
	}
	
}
?>